﻿
//
// (C) Copyright 2009 Irantha Suwandarathna (iranthas@hotmail.com)
// All rights reserved.
//

/********************************************************
 * ADO.NET 2.0 Data Provider for SQLite Version 3.X
 * Written by Robert Simpson (robert@blackcastlesoft.com)
 * 
 ********************************************************/


using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Globalization;
using System.Text;
using System.IO;
using EffiProzDB;

namespace System.Data.EffiProzDB
{

    public sealed partial class EffiProzDBConnection : DbConnection
    {

        private const string _dataDirectory = "|DataDirectory|";

        /// <summary>
        /// State of the current connection
        /// </summary>
        private ConnectionState _connectionState;
        /// <summary>
        /// The connection string
        /// </summary>
        private string _connectionString;
        /// <summary>
        /// Nesting level of the transactions open on the connection
        /// </summary>
        internal int _transactionLevel;

        /// <summary>
        /// The default isolation level for new transactions
        /// </summary>
        private IsolationLevel _defaultIsolation ;

        /// <summary>
        /// Whether or not the connection is enlisted in a distrubuted transaction
        /// </summary>
        internal EffiProzDBEnlistment _enlistment;


        /// <summary>
        /// The base EffiProzDB object to interop with
        /// </summary>
        internal SessionInterface sessionProxy;

        internal HsqlProperties connProperties;

        /// <summary>
        /// The database filename minus path and extension
        /// </summary>
        private string _dataSource;
    

        /// <summary>
        /// Default command timeout
        /// </summary>
        private int _defaultTimeout = 30;

        internal long _version;

        /**
        * Is this an internal connection?
        */
        internal bool isInternal = false;

        /** Is this connection to a network server instance. */
        bool isNetConn = false;

        string connectionType;

        //command line options
        Dictionary<string, string> opts;

        //connectionPoolKey for this connection
        string _poolKey;

        int _poolVersion;

        bool _usePooling;

        /// <summary>
        /// This event is raised whenever the database is opened or closed.
        /// </summary>
        public override event StateChangeEventHandler StateChange;

        private string _user = "";

        ///<overloads>
        /// Constructs a new EffiProzDBConnection object
        /// </overloads>
        /// <summary>
        /// Default constructor
        /// </summary>
        public EffiProzDBConnection()
            : this("")
        {
        }

        /// <summary>
        /// Initializes the connection with the specified connection string
        /// </summary>
        /// <param name="connectionString">The connection string to use on the connection</param>
        public EffiProzDBConnection(string connectionString)
        {
            _connectionState = ConnectionState.Closed;
            _connectionString = "";
            //_commandList = new List<WeakReference>();

            if (connectionString != null)
                ConnectionString = connectionString;
        }

        /// <summary>
        /// Clones the settings and connection string from an existing connection.  If the existing connection is already open, this
        /// function will open its own connection, enumerate any attached databases of the original connection, and automatically
        /// attach to them.
        /// </summary>
        /// <param name="connection"></param>
        public EffiProzDBConnection(EffiProzDBConnection connection)
            : this(connection.ConnectionString)
        {
           
        }

        /// <summary>
        /// Clears the connection pool associated with the connection.  Any other active connections using the same database 
        /// will be discarded instead of returned to the pool when they are closed.
        /// </summary>
        /// <param name="connection"></param>
        public static void ClearPool(EffiProzDBConnection connection)
        {
            EffiProzDBConnectionPool.ClearPool(connection._poolKey);
           
        }

        /// <summary>
        /// Clears all connection pools.  Any active connections will be discarded instead of sent to the pool when they are closed.
        /// </summary>
        public static void ClearAllPools()
        {
            EffiProzDBConnectionPool.ClearAllPools();
        }
    

        /// <summary>
        /// Disposes of the EffiProzDBConnection, closing it if it is active.
        /// </summary>
        /// <param name="disposing">True if the connection is being explicitly closed.</param>
        protected override void Dispose(bool disposing)
        {
            base.Dispose(disposing);

            if (disposing)
                Close();
        }



        /// <summary>
        /// Raises the state change event when the state of the connection changes
        /// </summary>
        /// <param name="newState">The new state.  If it is different from the previous state, an event is raised.</param>
        internal void OnStateChange(ConnectionState newState)
        {
            ConnectionState oldState = _connectionState;
            _connectionState = newState;

            if (StateChange != null && oldState != newState)
            {
                StateChangeEventArgs e = new StateChangeEventArgs(oldState, newState);
                StateChange(this, e);
            }
        }

       
        /// <summary>
        /// Creates a new EffiProzDBTransaction if one isn't already active on the connection.
        /// </summary>
        /// <param name="isolationLevel">Supported isolation levels are Serializable, ReadCommitted and Unspecified.</param>
        /// <remarks>
        /// Unspecified will use the default isolation level specified in the connection string.  If no isolation level is specified in the 
        /// connection string, Serializable is used.
        /// Serializable transactions are the default.  In this mode, the engine gets an immediate lock on the database, and no other threads
        /// may begin a transaction.  Other threads may read from the database, but not write.
        /// With a ReadCommitted isolation level, locks are deferred and elevated as needed.  It is possible for multiple threads to start
        /// a transaction in ReadCommitted mode, but if a thread attempts to commit a transaction while another thread
        /// has a ReadCommitted lock, it may timeout or cause a deadlock on both threads until both threads' CommandTimeout's are reached.
        /// </remarks>
        /// <returns>Returns a EffiProzDBTransaction object.</returns>
        public new EffiProzDBTransaction BeginTransaction(IsolationLevel isolationLevel)
        {
            if (isolationLevel != IsolationLevel.ReadUncommitted)
                throw new NotSupportedException("Only read uncommited mode supported");
            return (EffiProzDBTransaction)BeginDbTransaction(isolationLevel);
        }

        /// <summary>
        /// Creates a new EffiProzDBTransaction if one isn't already active on the connection.
        /// </summary>
        /// <returns>Returns a EffiProzDBTransaction object.</returns>
        public new EffiProzDBTransaction BeginTransaction()
        {            
            return (EffiProzDBTransaction)BeginDbTransaction(_defaultIsolation);
        }

        /// <summary>
        /// Forwards to the local BeginTransaction() function
        /// </summary>
        /// <param name="isolationLevel">Supported isolation levels are Unspecified, Serializable, and ReadCommitted</param>
        /// <returns></returns>
        protected override DbTransaction BeginDbTransaction(IsolationLevel isolationLevel)
        {
            if (_connectionState != ConnectionState.Open)
                throw new InvalidOperationException();

            if (isolationLevel == IsolationLevel.Unspecified) isolationLevel = _defaultIsolation;

            if (isolationLevel != IsolationLevel.ReadUncommitted)
                throw new ArgumentException("isolationLevel");

            return new EffiProzDBTransaction(this, isolationLevel);
        }

        /// <summary>
        /// Not implemented
        /// </summary>
        /// <param name="databaseName"></param>
        public override void ChangeDatabase(string databaseName)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// When the database connection is closed, all commands linked to this connection are automatically reset.
        /// </summary>
        public override void Close()
        {
            if (_enlistment != null)
            {
                lock (_enlistment)
                {
                    // If the connection is enlisted in a transaction scope and the scope is still active,
                    // we cannot truly shut down this connection until the scope has completed.  Therefore make a 
                    // hidden connection temporarily to hold open the connection until the scope has completed.
                    EffiProzDBConnection cnn = new EffiProzDBConnection();
                    cnn.sessionProxy = sessionProxy;
                    cnn._transactionLevel = _transactionLevel;
                    cnn._enlistment = _enlistment;
                    cnn._connectionState = _connectionState;
                    cnn._version = _version;

                    cnn._enlistment._transaction._cnn = cnn;
                    cnn._enlistment._disposeConnection = true;
                    sessionProxy = null;
                    _enlistment = null;
                }
            }



            if (sessionProxy != null)
            {
                if (_usePooling && !sessionProxy.isClosed())
                {
                    try
                    {
                        //clear any pending transaction
                        if (!sessionProxy.isAutoCommit())
                        {
                            Result reso = new Result(ResultConstants.SQLEXECDIRECT);
                            reso.setMainString("ROLLBACK;");

                            Result resi = sessionProxy.execute(reso);
                            if (resi.isError())
                                throw new Exception();

                        }

                        sessionProxy.setAutoCommit(true); //restore defaults
                        sessionProxy.setReadOnly(false);
                        EffiProzDBConnectionPool.Add(_poolKey, sessionProxy, _poolVersion);
                        sessionProxy = null;
                    }
                    catch (Exception)
                    {
                    }
                }
                
                if(sessionProxy != null)
                {
                    try
                    {
                        if (!isNetConn)
                        {
                            Result reso = new Result(ResultConstants.SQLEXECDIRECT);
                            reso.setMainString("SHUTDOWN;");
                            Result resi = sessionProxy.execute(reso);
                        }
                        sessionProxy.close();
                    }
                    catch (Exception) { }
                    
                    sessionProxy = null;
                }

                 
                _transactionLevel = 0;
            }
            OnStateChange(ConnectionState.Closed);
        }


        /// <summary>
        /// Manual distributed transaction enlistment support
        /// </summary>
        /// <param name="transaction">The distributed transaction to enlist in</param>
        public override void EnlistTransaction(System.Transactions.Transaction transaction)
        {
            if (_transactionLevel > 0 && transaction != null)
                throw new ArgumentException("Unable to enlist in transaction, a local transaction already exists");

            if (_enlistment != null && transaction == _enlistment._scope)
                return;
            else if (_enlistment != null)
                throw new ArgumentException("Already enlisted in a transaction");

            _enlistment = new EffiProzDBEnlistment(this, transaction);
        }

       

        /// <summary>
        /// The connection string containing the parameters for the connection
        /// </summary>
        /// <remarks>
        /// <list type="table">
        /// <listheader>
        /// <term>Parameter</term>
        /// <term>Values</term>
        /// <term>Required</term>
        /// <term>Default</term>
        /// </listheader>
        /// <item>
        /// <description>Data Source</description>
        /// <description>{filename or databasename}</description>
        /// <description>Y</description>
        /// <description></description>
        /// </item>
        /// <item>
        /// <description>Version</description>
        /// <description>3</description>
        /// <description>N</description>
        /// <description>3</description>
        /// </item>
        /// <item>
        /// <description>Type</description>
        /// <description>Memory | File | Server</description>
        /// <description>N</description>
        /// <description>False</description>
        /// </item>   
        /// <item>
        /// <description>User</description>
        /// <description>{user}</description>
        /// <description>N</description>
        /// <description></description>
        /// </item>    
        /// <item>
        /// <description>Password</description>
        /// <description>{password}</description>
        /// <description>N</description>
        /// <description></description>
        /// </item>    
        /// <item>
        /// <description>Pooling</description>
        /// <description><b>True</b> - Use connection pooling<br/><b>False</b> - Do not use connection pooling</description>
        /// <description>N</description>
        /// <description>False</description>
        /// </item>  
        /// <item>
        /// <description>Default Timeout</description>
        /// <description>{time in seconds}<br/>The default command timeout</description>
        /// <description>N</description>
        /// <description>30</description>
        /// </item>
        /// <item>
        /// <description>Max Pool Size</description>
        /// <description>The maximum number of connections for the given connection string that can be in the connection pool</description>
        /// <description>N</description>
        /// <description>100</description>
        /// </item>
        /// </list>
        /// </remarks>

        public override string ConnectionString
        {
            get
            {
                return _connectionString;
            }
            set
            {
                if (value == null)
                    throw new ArgumentNullException();

                else if (_connectionState != ConnectionState.Closed)
                    throw new InvalidOperationException();

                _connectionString = value;
            }
        }

        /// <summary>
        /// Create a new EffiProzDBCommand and associate it with this connection.
        /// </summary>
        /// <returns>Returns an instantiated EffiProzDBCommand object already assigned to this connection.</returns>
        public new EffiProzDBCommand CreateCommand()
        {
            return new EffiProzDBCommand(this);
        }

        /// <summary>
        /// Forwards to the local CreateCommand() function
        /// </summary>
        /// <returns></returns>
        protected override DbCommand CreateDbCommand()
        {
            return CreateCommand();
        }

        /// <summary>
        /// Returns the filename without extension or path
        /// </summary>
        public override string DataSource
        {
            get
            {
                return _dataSource;
            }
        }

        /// <summary>
        /// Returns an empty string
        /// </summary>
        public override string Database
        {
            get
            {
                return "";
            }
        }

        internal static string MapUriPath(string path)
        {
            if (path.StartsWith("file://", StringComparison.OrdinalIgnoreCase))
                return path.Substring(7);
            else if (path.StartsWith("file:", StringComparison.OrdinalIgnoreCase))
                return path.Substring(5);
            return path;           
        }

        /// <summary>
        /// Parses the connection string into component parts
        /// </summary>
        /// <param name="connectionString">The connection string to parse</param>
        /// <returns>An array of key-value pairs representing each parameter of the connection string</returns>
        internal static Dictionary<string, string> ParseConnectionString(string connectionString)
        {
            string s = connectionString;
            int n;
            Dictionary<string, string> ls = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);

            // First split into semi-colon delimited values.  The Split() function of EffiProzDBBase accounts for and properly
            // skips semi-colons in quoted strings
            string[] arParts = s.Split( ';');
            string[] arPiece;

            int x = arParts.Length;
            // For each semi-colon piece, split into key and value pairs by the presence of the = sign
            for (n = 0; n < x; n++)
            {
                arPiece =arParts[n].Split( '=');
                if (arPiece.Length == 2)
                {
                    ls.Add(arPiece[0].Trim(), arPiece[1].Trim());
                }
                else if (arPiece.Length == 1)
                {
                    ls.Add(arPiece[0].Trim(), "");
                }
                else throw new ArgumentException(String.Format(CultureInfo.CurrentCulture, "Invalid ConnectionString format for parameter \"{0}\"", (arPiece.Length > 0) ? arPiece[0] : "null"));
            }
            return ls;
        }


        /// <summary>
        /// Looks for a key in the array of key/values of the parameter string.  If not found, return the specified default value
        /// </summary>
        /// <param name="items">The list to look in</param>
        /// <param name="key">The key to find</param>
        /// <param name="defValue">The default value to return if the key is not found</param>
        /// <returns>The value corresponding to the specified key, or the default value if not found.</returns>
        static internal string FindKey(Dictionary<string, string> items, string key, string defValue)
        {
            string ret;

            if (items.TryGetValue(key, out ret)) return ret;

            return defValue;
        }

        /// <summary>
        /// Opens the connection using the parameters found in the <see cref="ConnectionString">ConnectionString</see>
        /// </summary>
        public override void Open()
        {
            if (_connectionState != ConnectionState.Closed)
                throw new InvalidOperationException();

            Close();

            opts = ParseConnectionString(_connectionString);
            string fileName;
            string host = String.Empty;
            int port = 0;

            fileName = FindKey(opts, "Data Source", "");
            if (String.IsNullOrEmpty(fileName))
                throw new ArgumentException("Data Source cannot be empty.");

            string _type =FindKey(opts, "Type", "Memory");
            if (_type.Equals("Memory",StringComparison.OrdinalIgnoreCase))
            {
                connectionType = DatabaseURL.S_MEM;
                _poolKey = "Mem:"+fileName;
            }
            else if (_type.Equals("File", StringComparison.OrdinalIgnoreCase))
            {
                connectionType = DatabaseURL.S_FILE;
                fileName = MapUriPath(fileName);
                fileName = ExpandFileName(fileName);
                _poolKey = "File:" + fileName;
            }
            else if (_type.Equals("Server", StringComparison.OrdinalIgnoreCase))
            {              
                string proto = FindKey(opts, "Protocol", "HSQL");
               
                if(proto.Equals("HTTP", StringComparison.OrdinalIgnoreCase))
                    connectionType = DatabaseURL.S_HTTP;
                else
                    connectionType = DatabaseURL.S_HSQL;

                host = FindKey(opts, "Host", "localhost");
                port = Convert.ToInt32( FindKey(opts, "Port", "9001"));              
                _poolKey = connectionType+"://" +host+":"+port+"/" +fileName;
            }
            else {
               
                throw new NotImplementedException();
            }         
                 
            try
            {
                _usePooling = Convert.ToBoolean(FindKey(opts, "Pooling", Boolean.FalseString)) ;
                bool autoCommit = Convert.ToBoolean(FindKey(opts, "AutoCommit", Boolean.TrueString));
                bool readOnly = Convert.ToBoolean(FindKey(opts, "ReadOnly", Boolean.FalseString));
                int maxPoolSize = Convert.ToInt32(FindKey(opts, "Max Pool Size", "100"), CultureInfo.InvariantCulture);

                _defaultTimeout = Convert.ToInt32(FindKey(opts, "Default Timeout", "30"), CultureInfo.CurrentCulture);

                _defaultIsolation = (IsolationLevel)Enum.Parse(typeof(IsolationLevel), FindKey(opts, "Default IsolationLevel", "ReadUncommitted"), true);
                if (_defaultIsolation != IsolationLevel.ReadUncommitted)
                    throw new NotSupportedException("Invalid Default IsolationLevel specified");

                string password = FindKey(opts, "Password", "");
                _user = FindKey(opts, "User", "");

                _poolKey = "User:" + _user + "Pass:" + password+_poolKey;

                connProperties = new HsqlProperties();
                connProperties.addProperties(opts);

                if (_usePooling)
                {
                   sessionProxy = EffiProzDBConnectionPool.Remove(_poolKey, maxPoolSize, out _poolVersion);
                   if (sessionProxy != null && sessionProxy.isClosed())
                       sessionProxy = null;
                   //Result rso = new Result(ResultConstants.SQLEXECDIRECT);
                   //rso.setMainString("CONNECT USER " + user + " PASSWORD " + password + ";");
                   //try
                   //{
                   //    Result rsi = sessionProxy.execute(rso);
                   //    if (rsi.isError())
                   //    {
                   //        sessionProxy.close();
                   //        throw new Exception();
                   //    }

                   //}
                   //catch (Exception)
                   //{
                   //    sessionProxy = null;
                   //}
                }

                if (sessionProxy == null)
                {
                    if (DatabaseURL.isInProcessDatabaseType(connectionType))
                    {
                        sessionProxy = DatabaseManager.newSession(connectionType, fileName, _user, password, connProperties);
                    }
                    else if (connectionType.Equals(DatabaseURL.S_HSQL,StringComparison.OrdinalIgnoreCase))
                    {
                        isNetConn = true;
                        sessionProxy = new HSQLClientConnection(host, port, "", fileName, false, _user, password);
                    }
                    else if (connectionType.Equals(DatabaseURL.S_HTTP,StringComparison.OrdinalIgnoreCase))
                    {
                        isNetConn = true;
                        sessionProxy = new HTTPClientConnection(host, port, "", fileName, false, _user, password);
                    }
                    else
                    {
                        throw new ArgumentException("Invalid Protocol : " + connectionType);
                    }
                }


                if (!autoCommit)
                    sessionProxy.setAutoCommit(false);

                if (readOnly)
                    sessionProxy.setReadOnly(true);

                _dataSource = Path.GetFileNameWithoutExtension(fileName);

                _version++;

                ConnectionState oldstate = _connectionState;
                _connectionState = ConnectionState.Open;

                if (Transactions.Transaction.Current != null &&
                  Convert.ToBoolean(FindKey(opts, "Enlist", Boolean.TrueString)) == true)
                    EnlistTransaction(Transactions.Transaction.Current);

                _connectionState = oldstate;              
                OnStateChange(ConnectionState.Open);
              

            }
            catch (HsqlException e)
            {
                Close();
                throw EffiProzDBException.GetException(e);
            }
            catch (EffiProzDBException)
            {
                Close();
                throw;
            }
        }

        /// <summary>
        /// Gets/sets the default command timeout for newly-created commands.  This is especially useful for 
        /// commands used internally such as inside a EffiProzDBTransaction, where setting the timeout is not possible.
        /// This can also be set in the ConnectionString with "Default Timeout"
        /// </summary>
        public int DefaultTimeout
        {
            get { return _defaultTimeout; }
            set { _defaultTimeout = value; }
        }

        /// <summary>
        /// Returns the version of the underlying EffiProzDB database engine
        /// </summary>
        public override string ServerVersion
        {
            get
            {
                return EffiProzDBVersion;
                //if (_connectionState != ConnectionState.Open)
                //  throw new InvalidOperationException();

                //return _sql.Version;
            }
        }

        /// <summary>
        /// Returns the version of the underlying EffiProzDB database engine
        /// </summary>
        public static string EffiProzDBVersion
        {
            get { return "EffiProzDB v0.01test"; }
        }

        /// <summary>
        /// Returns the state of the connection.
        /// </summary>
        public override ConnectionState State
        {
            get
            {
                if (sessionProxy== null || sessionProxy.isClosed())
                    _connectionState = ConnectionState.Closed;

                return  _connectionState;
            }
        }
      
      
        /// <summary>
        /// Expand the filename of the data source, resolving the |DataDirectory| macro as appropriate.
        /// </summary>
        /// <param name="sourceFile">The database filename to expand</param>
        /// <returns>The expanded path and filename of the filename</returns>
        private string ExpandFileName(string sourceFile)
        {
            if (String.IsNullOrEmpty(sourceFile)) return sourceFile;

            if (sourceFile.StartsWith(_dataDirectory, StringComparison.OrdinalIgnoreCase))
            {
                string dataDirectory;


                dataDirectory = AppDomain.CurrentDomain.GetData("DataDirectory") as string;
                if (String.IsNullOrEmpty(dataDirectory))
                    dataDirectory = AppDomain.CurrentDomain.BaseDirectory;


                if (sourceFile.Length > _dataDirectory.Length)
                {
                    if (sourceFile[_dataDirectory.Length] == Path.DirectorySeparatorChar ||
                        sourceFile[_dataDirectory.Length] == Path.AltDirectorySeparatorChar)
                        sourceFile = sourceFile.Remove(_dataDirectory.Length, 1);
                }
                sourceFile = Path.Combine(dataDirectory, sourceFile.Substring(_dataDirectory.Length));
            }

            if (sourceFile.StartsWith("\"") && sourceFile.EndsWith("\""))
                sourceFile = sourceFile.Substring(1, sourceFile.Length - 2);

            sourceFile = Path.GetFullPath(sourceFile);

            return sourceFile;
        }

    
        /// <returns>A DataTable of the MetaDataCollections schema</returns>
        public override DataTable GetSchema()
        {
            return GetSchema("MetaDataCollections", null);
        }

        /// <summary>
        /// Returns schema information of the specified collection
        /// </summary>
        /// <param name="collectionName">The schema collection to retrieve</param>
        /// <returns>A DataTable of the specified collection</returns>
        public override DataTable GetSchema(string collectionName)
        {
            return GetSchema(collectionName, new string[0]);
        }

        /// <summary>
        /// Retrieves schema information using the specified constraint(s) for the specified collection
        /// </summary>
        /// <param name="collectionName">The collection to retrieve</param>
        /// <param name="restrictionValues">The restrictions to impose</param>
        /// <returns>A DataTable of the specified collection</returns>
        public override DataTable GetSchema(string collectionName, string[] restrictionValues)
        {
            if (_connectionState != ConnectionState.Open)
                throw new InvalidOperationException();

            EffiProzDBMetaData md = new EffiProzDBMetaData(this);

            return md.GetMetaData(collectionName, restrictionValues);
        
        }

        public bool SetAutoCommit(bool enable)
        {
            bool oldEnable = sessionProxy.isAutoCommit();
            sessionProxy.setAutoCommit(enable);
            return oldEnable;
        }

        /// <summary>
        /// Change the password (or assign a password) to an open database.
        /// </summary>
        /// <remarks>
        /// No readers or writers may be active for this process.  The database must already be open
        /// and if it already was password protected, the existing password must already have been supplied.
        /// </remarks>
        /// <param name="newPassword">The new password to assign to the database</param>
        public void ChangePassword(string newPassword)
        {
            if (_connectionState != ConnectionState.Open)
                throw new InvalidOperationException("Database must be opened before changing the password.");

            Result resultOut = new Result(ResultConstants.SQLEXECDIRECT);
            resultOut.setMainString("ALTER USER "+_user+"  SET PASSWORD \""+ newPassword+"\";");
            try
            {
                Result resultIn = sessionProxy.execute(resultOut);
                if (resultIn.isError())
                    throw resultIn.getException();
            }
            catch (HsqlException e)
            {
                throw EffiProzDBException.GetException(e);
            }
           
        }
          
    }

}
